﻿-- Function: pl_function.guardar_mhresum(character varying, character varying, integer)

DROP FUNCTION IF EXISTS pl_function.guardar_mhresum(character varying, character varying, integer, character varying);

CREATE OR REPLACE FUNCTION pl_function.guardar_mhresum(p_text character varying, p_dpredio character varying, p_tipoacc integer, p_mpropie character varying)
  RETURNS character varying AS
$BODY$
declare
	p_actuali character varying(1) := '1';
	p_vnrodoc character varying(10) := '';
	p_idsigma character varying(10) := '';
	p_mperson character varying(10) := '';
	p_vhostnm character varying(25) := '';
	p_vusernm character varying(25) := '';
	p_cperini character(4) := '';
	p_cperfin character(4) := '';
	p_nhresum_old character varying(10) := '';
begin
	select mperson, idsigma, vnrodoc, cperini, coalesce(cperfin, to_char(now(), 'yyyy')), vhostnm, vusernm
	into p_mperson, p_idsigma, p_vnrodoc, p_cperini, p_cperfin, p_vhostnm, p_vusernm
	from pl_table.tmp_mhresum(p_text);

	if p_idsigma = '-1' then
		select idsigma into p_nhresum_old from registro.mhresum where mperson=p_mperson and nestado=1;
	
		p_actuali := '0';
		select lpad((coalesce(trim(max(idsigma))::numeric(18, 0), 0) + 1)::varchar, 10, '0') into p_idsigma 
		from registro.mhresum;
		select to_char(now(), 'yyyy') || lpad((coalesce(right(trim(max(vnrodoc)), 6)::numeric(18, 0), 0) + 1)::varchar, 6, '0') into p_vnrodoc 
		from registro.mhresum where left(vnrodoc, 4)=to_char(now(), 'yyyy');

		update registro.mhresum set nestado=0, cperfin=to_char(now(), 'yyyy') where mperson=p_mperson and nestado=1;
	end if;
	
	with new_values(
		idsigma, ctipdat, vnrodoc, dfecdoc,
		cmotivo, ctipdoc, vnrodjj, vobserv,
		nestado, vhostnm, vusernm, ddatetm,
		cperini, cperfin, mperson
	) as (
		select
			idsigma, ctipdat, p_vnrodoc vnrodoc, dfecdoc,
			cmotivo, ctipdoc, vnrodjj, vobserv,
			nestado, vhostnm, vusernm, ddatetm,
			cperini, cperfin, mperson
		from pl_table.tmp_mhresum(p_text)
	), upsert as ( 
	    update registro.mhresum m set
			  ctipdat=n.ctipdat, vnrodoc=n.vnrodoc, dfecdoc=n.dfecdoc,
			  cmotivo=n.cmotivo, ctipdoc=n.ctipdoc, vnrodjj=n.vnrodjj,
			  vobserv=n.vobserv, nestado=n.nestado, vhostnm=n.vhostnm,
			  vusernm=n.vusernm, ddatetm=n.ddatetm, cperini=n.cperini,
			  cperfin=n.cperfin, mperson=n.mperson
	    from new_values n
	    where m.idsigma = n.idsigma
	    returning m.*
	)
	insert into registro.mhresum(
		idsigma, ctipdat, vnrodoc, dfecdoc,
		cmotivo, ctipdoc, vnrodjj, vobserv,
		nestado, vhostnm, vusernm, ddatetm,
		cperini, cperfin, mperson
	) select p_idsigma idsigma,
		ctipdat, vnrodoc, dfecdoc, cmotivo,
		ctipdoc, vnrodjj, vobserv, nestado,
		vhostnm, vusernm, ddatetm, cperini,
		null cperfin, mperson
	from new_values where not exists(select 1 from upsert where upsert.idsigma=new_values.idsigma);

	if p_actuali = '0' then
		perform pl_function.copiar_declaracion(p_idsigma, p_nhresum_old, p_dpredio, p_tipoacc, p_cperini::int, p_cperfin::int, p_vhostnm, p_vusernm, p_mpropie);
	end if;
	
	return p_vnrodoc;
exception
when sqlstate '57014' then
	return 'Error al registrar.';
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION pl_function.guardar_mhresum(character varying, character varying, integer, character varying)
  OWNER TO postgres;
GRANT EXECUTE ON FUNCTION pl_function.guardar_mhresum(character varying, character varying, integer, character varying) TO postgres;
GRANT EXECUTE ON FUNCTION pl_function.guardar_mhresum(character varying, character varying, integer, character varying) TO public;
GRANT EXECUTE ON FUNCTION pl_function.guardar_mhresum(character varying, character varying, integer, character varying) TO desarrollo;
